package jdbc;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author lzy
 * @create 2018-07-31 下午3:45
 */
public class DbutilsTest {
    private static final Logger LOGGER = LoggerFactory.getLogger(DbutilsTest.class);
    // 创建QueryRunner的实现类
    private QueryRunner queryRunner = new QueryRunner();

    @Test
    public void testScalarHandler() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT name FROM customers WHERE id=?";
            String result = queryRunner.query(connection, sql, new ScalarHandler<>(), 10);
            LOGGER.info(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null);
        }
    }

    /**
     * MapListHandler:将结果集转换成一个Map的List
     * Map对应查询的一条记录：
     * - 键：Sql查询的列名（不是列别名）
     * - 值：列的值
     * 而MapListHandler：返回多条记录对应的Map的集合
     */
    @Test
    public void testMapListHandler() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT id, name, email, birth FROM customers";
            List<Map<String, Object>> mapList = queryRunner.query(connection, sql, new MapListHandler());
            LOGGER.info(mapList.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null);
        }
    }

    /**
     * MapHandler:返回sql对应的第一条记录对应的Map对象
     * 键：Sql查询的列名（不是列别名）
     * 值：列的值
     */
    @Test
    public void testMapHandler() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT id, name, email, birth FROM customers";
            Map<String, Object> map = queryRunner.query(connection, sql, new MapHandler());
            LOGGER.info(map.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null);
        }
    }

    /**
     * BeanListHandler:把结果集转换为一个List，该List不为null，但可能为空集合（size()方法返回0）
     * 若SQL语句的确能够查询到记录，List存放创建按BeanListHandler传入的Class类型对应的对象
     */
    @Test
    public void testBeanListHandler() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT id, name, email, birth FROM customers";
            List<Customer> customers = queryRunner.query(connection, sql, new BeanListHandler<>(Customer.class));
            LOGGER.info(customers.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null);
        }
    }

    /**
     * BeanHandler:把结果集的第一条记录转换为创建BeanHandler对象传入的Class
     * 参数对应的对象
     */
    @Test
    public void testBeanHandler() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT id, name, email, birth FROM customers WHERE id=?";
            Customer customer = queryRunner.query(connection, sql, new BeanHandler<>(Customer.class), 10);
            LOGGER.info(customer.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null);
        }
    }

    /**
     * 测试query方法
     */
    @Test
    public void testQueryRunnerQuery() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT id, name, email, birth FROM customers";
            Object result = queryRunner.query(connection, sql, new MyResultSetHandler());
            LOGGER.info(result.toString());
//            List<Customer> customers = queryRunner.query(connection,sql,new BeanListHandler<>(Customer.class));
//            customers.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null);
        }
    }

    /**
     * 测试QueryRunner类的update方法
     */
    @Test
    public void testQueryRunnerUpdate() {
        // 使用其update方法
        String sql = "DELETE FROM customers WHERE id in (?,?)";

        Connection connection = null;

        try {
            connection = JdbcUtils.getConnection();
            queryRunner.update(connection, sql, 8, 9);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null);
        }

    }

    private class MyResultSetHandler implements ResultSetHandler {
        @Override
        public Object handle(ResultSet rs) throws SQLException {
            LOGGER.info("this is my result handler");
            List<Customer> customers = new ArrayList<>();
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                String birth = rs.getString(4);

                Customer customer = new Customer(id, name, email, birth);
                customers.add(customer);
            }
            return customers;
        }
    }
}
